package it.trekker.db;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class ExcursionCoordinateDAO {

	private static final String TAG = ExcursionCoordinateDAO.class.getSimpleName();
	
	private static Context context = null;
	private SQLiteDatabase db = null;
	private DatabaseHelper dbHelper = null;
	
	private SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	public ExcursionCoordinateDAO(Context context) {
		dbHelper = new DatabaseHelper(context);
	}

	public SQLiteDatabase open() {
		if(db == null || !db.isOpen()) {
			db = dbHelper.getWritableDatabase();
			db.execSQL("PRAGMA foreign_keys=ON;");
		}
		
		return db;
	}

	public void close() {
		Log.d(TAG, "close db");
		if(db != null && db.isOpen())
			db.close();
	}
	
	public boolean isOpen() {
		return db.isOpen();
	}
	
	public long insertGPSCoordinate(ExcursionCoordinate coordinate) {

		Log.d(TAG, "insertGPSCoordinate");
		
		ContentValues cv = new ContentValues();

		cv.put(ExcursionCoordinateTableMeta.KEY_LATITUDE, coordinate.getCoordinate().getLatitude());
		cv.put(ExcursionCoordinateTableMeta.KEY_LONGITUDE, coordinate.getCoordinate().getLongitude());
		cv.put(ExcursionCoordinateTableMeta.KEY_ALTITUDE, coordinate.getCoordinate().getAltitude());
//		cv.put(GPSCoordinateTableMeta.KEY_TIME, coordinate.getTime());
//		cv.put(GPSCoordinateTableMeta.KEY_TIME, dateFormat.format(coordinate.getTime()));
		cv.put(ExcursionCoordinateTableMeta.KEY_EXCURSIONID, coordinate.getExcursionId());
		
		return db.insert(ExcursionCoordinateTableMeta.DATABASE_TABLE, null, cv);
		
	}

	public boolean deleteGPSCoordinate(long id) {
		return (db.delete(ExcursionCoordinateTableMeta.DATABASE_TABLE, ExcursionCoordinateTableMeta.KEY_ROWID + "=" + id, null) > 0);
	}

	public List<ExcursionCoordinate> getAllCoordinates() {

		List<ExcursionCoordinate> result = null;

		Cursor cursor = db.query(ExcursionCoordinateTableMeta.DATABASE_TABLE, new String[] {
				ExcursionCoordinateTableMeta.KEY_ROWID,
				ExcursionCoordinateTableMeta.KEY_LATITUDE,
				ExcursionCoordinateTableMeta.KEY_LONGITUDE,
				ExcursionCoordinateTableMeta.KEY_ALTITUDE,
				ExcursionCoordinateTableMeta.KEY_TIME, 
				ExcursionCoordinateTableMeta.KEY_EXCURSIONID },
				null,
				null,
				null,
				null,
				null);

		if(cursor != null) {
			result = new ArrayList<ExcursionCoordinate>();

			while(cursor.moveToNext()) {
				ExcursionCoordinate coordinate = cursorToGPSCoordinate(cursor);
				result.add(coordinate);
			}
			cursor.close();
		}

		return result;
	}
	
	public List<ExcursionCoordinate> getAllCoordinatesByExcursion(long excursionId) {

		List<ExcursionCoordinate> result = null;

		Cursor cursor = db.query(ExcursionCoordinateTableMeta.DATABASE_TABLE, new String[] {
				ExcursionCoordinateTableMeta.KEY_ROWID,
				ExcursionCoordinateTableMeta.KEY_LATITUDE,
				ExcursionCoordinateTableMeta.KEY_LONGITUDE,
				ExcursionCoordinateTableMeta.KEY_ALTITUDE,
				ExcursionCoordinateTableMeta.KEY_TIME, 
				ExcursionCoordinateTableMeta.KEY_EXCURSIONID },
				ExcursionCoordinateTableMeta.KEY_EXCURSIONID + "=" + excursionId,
				null,
				null,
				null,
				null);

		if(cursor != null) {
			result = new ArrayList<ExcursionCoordinate>();

			while(cursor.moveToNext()) {
				ExcursionCoordinate coordinate = cursorToGPSCoordinate(cursor);
				result.add(coordinate);
			}
			cursor.close();
		}

		return result;
	}
	
	public Cursor getAllCoordinatesByExcursionAsCursor(long excursionId) {

		Cursor result = db.query(ExcursionCoordinateTableMeta.DATABASE_TABLE, new String[] {
				ExcursionCoordinateTableMeta.KEY_ROWID,
				ExcursionCoordinateTableMeta.KEY_LATITUDE,
				ExcursionCoordinateTableMeta.KEY_LONGITUDE,
				ExcursionCoordinateTableMeta.KEY_ALTITUDE,
				ExcursionCoordinateTableMeta.KEY_TIME, 
				ExcursionCoordinateTableMeta.KEY_EXCURSIONID },
				ExcursionCoordinateTableMeta.KEY_EXCURSIONID + "=" + excursionId,
				null,
				null,
				null,
				null);

		return result;
	}
	
	public List<ExcursionCoordinate> getAllCoordinatesAfterPoint(ExcursionCoordinate point) {
		
		List<ExcursionCoordinate> result = null;
		
		Cursor cursor = db.query(ExcursionCoordinateTableMeta.DATABASE_TABLE, new String[] {
				ExcursionCoordinateTableMeta.KEY_ROWID,
				ExcursionCoordinateTableMeta.KEY_LATITUDE,
				ExcursionCoordinateTableMeta.KEY_LONGITUDE,
				ExcursionCoordinateTableMeta.KEY_ALTITUDE,
				ExcursionCoordinateTableMeta.KEY_TIME, 
				ExcursionCoordinateTableMeta.KEY_EXCURSIONID },
				ExcursionCoordinateTableMeta.KEY_TIME + ">" + point.getTimestamp() ,
				null,
				null,
				null,
				null);

		if(cursor != null) {
			result = new ArrayList<ExcursionCoordinate>();

			while(cursor.moveToNext()) {
				ExcursionCoordinate coordinate = cursorToGPSCoordinate(cursor);
				result.add(coordinate);
			}
			cursor.close();
		}

		return result;
	}
	
	public Cursor getAllCoordinatesAfterPointAsCursor(ExcursionCoordinate point) {
		Cursor result = db.query(ExcursionCoordinateTableMeta.DATABASE_TABLE, new String[] {
				ExcursionCoordinateTableMeta.KEY_ROWID,
				ExcursionCoordinateTableMeta.KEY_LATITUDE,
				ExcursionCoordinateTableMeta.KEY_LONGITUDE,
				ExcursionCoordinateTableMeta.KEY_ALTITUDE,
				ExcursionCoordinateTableMeta.KEY_TIME, 
				ExcursionCoordinateTableMeta.KEY_EXCURSIONID },
				ExcursionCoordinateTableMeta.KEY_TIME + ">" + point.getTimestamp() ,
				null,
				null,
				null,
				null);

		return result;
	}

	public ExcursionCoordinate getGPSCoordinate(long id) {

		ExcursionCoordinate result = null;
		
		Cursor cursor = db.query(ExcursionCoordinateTableMeta.DATABASE_TABLE, new String[] {
				ExcursionCoordinateTableMeta.KEY_ROWID,
				ExcursionCoordinateTableMeta.KEY_LATITUDE,
				ExcursionCoordinateTableMeta.KEY_LONGITUDE,
				ExcursionCoordinateTableMeta.KEY_ALTITUDE,
				ExcursionCoordinateTableMeta.KEY_TIME, 
				ExcursionCoordinateTableMeta.KEY_EXCURSIONID },
				ExcursionCoordinateTableMeta.KEY_ROWID + "=" + id,
				null,
				null,
				null,
				null);

		if (cursor != null) {
			cursor.moveToFirst();

			result = cursorToGPSCoordinate(cursor);

			cursor.close();
		}

		return result;
	}

	public boolean updateGPSCoordinate(ExcursionCoordinate coordinate) {
	
		ContentValues args = new ContentValues();

		args.put(ExcursionCoordinateTableMeta.KEY_LATITUDE, coordinate.getCoordinate().getLatitude());
		args.put(ExcursionCoordinateTableMeta.KEY_LONGITUDE, coordinate.getCoordinate().getLongitude());
		args.put(ExcursionCoordinateTableMeta.KEY_ALTITUDE, coordinate.getCoordinate().getAltitude());
		args.put(ExcursionCoordinateTableMeta.KEY_EXCURSIONID, coordinate.getExcursionId());

		return (db.update(ExcursionCoordinateTableMeta.DATABASE_TABLE, args, ExcursionCoordinateTableMeta.KEY_ROWID + "=" + coordinate.getId(), null) > 0);
	}

	private ExcursionCoordinate cursorToGPSCoordinate(Cursor cursor) {

		ExcursionCoordinate excursionCoord = null;

		if(cursor != null) {

			excursionCoord = new ExcursionCoordinate();
			GPSCoordinate gpsCoord = new GPSCoordinate();
			
			int idCol = cursor.getColumnIndexOrThrow(ExcursionCoordinateTableMeta.KEY_ROWID);
			int latitudeCol = cursor.getColumnIndexOrThrow(ExcursionCoordinateTableMeta.KEY_LATITUDE);
			int longitudeCol = cursor.getColumnIndexOrThrow(ExcursionCoordinateTableMeta.KEY_LONGITUDE);
			int altitudeCol = cursor.getColumnIndexOrThrow(ExcursionCoordinateTableMeta.KEY_ALTITUDE);
			int timeCol = cursor.getColumnIndexOrThrow(ExcursionCoordinateTableMeta.KEY_TIME);
			int excursionIdCol = cursor.getColumnIndexOrThrow(ExcursionCoordinateTableMeta.KEY_EXCURSIONID);

			excursionCoord.setId(cursor.getLong(idCol));
			gpsCoord.setLatitude(cursor.getDouble(latitudeCol));
			gpsCoord.setLongitude(cursor.getDouble(longitudeCol));
			gpsCoord.setAltitude(cursor.getDouble(altitudeCol));
			excursionCoord.setCoordinate(gpsCoord);
			try {
				excursionCoord.setTimestamp(dateFormat.parse(cursor.getString(timeCol)));
			} catch (ParseException e) {
				e.printStackTrace();
			}
			excursionCoord.setExcursionId(cursor.getLong(excursionIdCol));
		}

		return excursionCoord;
	}
}
